﻿#include "dbmanager.h"
#include <QCoreApplication>
#include <QSqlQuery>
#include <QSqlError>
#include <QTime>
#include <QDate>
#include <QNetworkInterface>


DBManager::DBManager()
{

}

bool DBManager::initDBManager()
{
    db = QSqlDatabase::addDatabase("QSQLITE");
    QString dbName =  QCoreApplication::applicationDirPath() + "/" + "chatdb.db";
    db.setDatabaseName(dbName);
    db.setPassword("chatonline");
    db.setUserName("chatonline");
    qDebug() << "dbName is " << dbName;
    qDebug() << "userName is " << db.userName();
    qDebug() << "userPassword is " << db.password();

    dbStatus = db.open();
    if(dbStatus){
        qDebug() << "db is ok...";

        QSqlQuery query;
        query.exec("CREATE TABLE IF NOT EXISTS TotalMsg("
                   "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
                   "name VARCHAR(20) NOT NULL, "
                   "number VARCHAR(20) NOT NULL, "
                   "msg TEXT NOT NULL, "
                   "time VARCHAR(20) NOT NULL, "
                   "self VARCHAR(20) NOT NULL)");
        qDebug() << "创建TotalMsg表：" << query.lastError();

        query.exec("CREATE TABLE IF NOT EXISTS TotalPerson("
                   "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
                   "number VARCHAR(20) NOT NULL, "
                   "name VARCHAR(20) NOT NULL, "
                   "img TEXT NOT NULL, "
                   "ip VARCHAR(20) NOT NULL, "
                   "port VARCHAR(20) NOT NULL)");
        qDebug() << "创建TotalPerson表：" << query.lastError();

        query.exec("CREATE TABLE IF NOT EXISTS UserInfo("
                   "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
                   "number VARCHAR(20) NOT NULL, "
                   "name VARCHAR(20) NOT NULL, "
                   "img TEXT, "
                   "ip VARCHAR(20) NOT NULL, "
                   "port VARCHAR(20) NOT NULL)");
        qDebug() << "创建UserInfo表：" << query.lastError();
        return true;
    }
    qDebug() << "数据库打开失败...";
    return false;

}

bool DBManager::updatetUserInfo(QString table, QString name, QString number, QString img)
{

    if(dbStatus){
        QSqlQuery query;
        bool success = query.exec(QString("UPDATE %1 SET name='%2', number='%3', img='%4' WHERE id=1").arg(table, name, number, img));
        if(success){
            qDebug() << "数据插入成功："<< query.lastQuery();
            return true;

        }
        else{
            qCritical() << "错误查询指令：" << query.lastQuery();
            qCritical() << "数据查询错误：" << query.lastError();
        }
    }
    return false;
}

QVariantList DBManager::queryMsgDate(QString table, QString number)
{
    QVariantList listResult;
    QVariantMap mapResult;
    if(dbStatus){
        QSqlQuery query;
        bool success = query.exec(QString("SELECT name, msg, time, self FROM %1 WHERE number=%2").arg(table, number));
        if(success){

            while(query.next()){
//                qDebug() << "数据库查询指令：" << query.lastQuery();
                qint32 name = query.value(0).toInt();
                QString msg = query.value(1).toString();
                QString time = query.value(2).toString();
                QString self = query.value(3).toString();

                mapResult.clear();
                mapResult.insert("name", name);
                mapResult.insert("msg", msg);
                mapResult.insert("time", time);
                mapResult.insert("self", self);

                listResult.append(mapResult);
            }

//            qDebug() << "数据查询成功："<< listResult;

        }
        else{
            qCritical() << "错误查询指令：" << query.lastQuery();
            qCritical() << "数据查询错误：" << query.lastError();
        }
    }
    return listResult;
}

bool DBManager::insertMsgDate(QString table, QString name, QString number, QString msg, QString self)
{
    QString _time;

    _time =  QDate::currentDate().toString("yyyy/MM/dd/") + QTime::currentTime().toString("hh:mm:ss");
    qDebug() << "当前时间为：" << _time;

    if(dbStatus){
        QSqlQuery query;
        bool success = query.exec(QString("INSERT INTO %1(name,number,msg,time,self) VALUES "
                                          "('%2', '%3', '%4', '%5', '%6')").arg(table, name, number, msg, _time, self));
        if(success){

            qDebug() << "数据插入成功："<< query.lastQuery();

        }
        else {
            qCritical() << "错误插入指令：" << query.lastQuery();
            qCritical() << "数据插入错误：" << query.lastError();
        }
    }
    return true;
}


QVariantList DBManager::queryDistinctPerson(QString table)
{
    QVariantList listResult;
    QVariantMap mapResult;
    if(dbStatus){
        QSqlQuery query;
        bool success = query.exec(QString("SELECT DISTINCT number, name, img, ip, port FROM %1").arg(table));
        if(success){

            qDebug() << "数据查询成功："<< query.lastQuery();
            while(query.next()){
                QString number = query.value(0).toString();
                QString name = query.value(1).toString();
                QString img = query.value(2).toString();
                QString ip = query.value(3).toString();
                QString port = query.value(4).toString();

                mapResult.clear();
                mapResult.insert("number", number);
                mapResult.insert("name", name);
                mapResult.insert("img", img);
                mapResult.insert("ip", ip);
                mapResult.insert("port", port);

                listResult.append(mapResult);
            }

        }
        else {
            qCritical() << "错误查询指令：" << query.lastQuery();
            qCritical() << "数据查询错误：" << query.lastError();
        }
    }
    return listResult;
}

bool DBManager::insertDistinctPerson(QString table, QString name, QString number, QString img, QString ip, QString port)
{
    if(dbStatus){
        QSqlQuery query;
        bool success = query.exec(QString("INSERT INTO %1(name,number,img,ip,port) VALUES "
                                          "('%2', '%3', '%4', '%5', '%6')").arg(table, name, number, img, ip, port));
        if(success){

            qDebug() << "数据插入成功："<< query.lastQuery();

        }
        else {
            qCritical() << "错误插入指令：" << query.lastQuery();
            qCritical() << "数据插入错误：" << query.lastError();
        }
    }
    return true;
}

QVariantList DBManager::queryNewMsg(QString table, QString number)
{
    QVariantList listResult;
    QVariantMap mapResult;
    if(dbStatus){
        QSqlQuery query;
        bool success = query.exec(QString("SELECT msg, time FROM %1 WHERE number=%2").arg(table, number));
        if(success){

            qDebug() << "数据查询成功："<< query.lastQuery();
            while(query.next()){
                QString msg = query.value(0).toString();
                QList listTime= query.value(1).toString().split("/");
                QString _time = listTime.last();

                mapResult.clear();
                mapResult.insert("msg", msg);
                mapResult.insert("time", _time);

                listResult.append(mapResult);
            }
//            qDebug() << "数据查询成功："<< listResult;

        }
        else {
            qCritical() << "错误查询指令：" << query.lastQuery();
            qCritical() << "数据查询错误：" << query.lastError();
        }
    }

    QVariantList lastListResult;
    if(listResult.length()){
        lastListResult.append(listResult.last());
    }

    qDebug() << "最新消息为："<< lastListResult;
    return lastListResult;
}

QVariantList DBManager::queryRecNewMsg(QString table, QString number)
{
    QVariantList listResult;
    QVariantMap mapResult;
    if(dbStatus){
        QSqlQuery query;
        bool success = query.exec(QString("SELECT msg, time FROM '%1' WHERE number='%2' AND self='' order by id desc LIMIT 1").arg(table, number));
        if(success){

            qDebug() << "数据查询成功："<< query.lastQuery();
            while(query.next()){
                QString msg = query.value(0).toString();
                QList listTime= query.value(1).toString().split("/");
                QString _time = listTime.last();

                mapResult.clear();
                mapResult.insert("msg", msg);
                mapResult.insert("time", _time);

                listResult.append(mapResult);
            }
//            qDebug() << "数据查询成功："<< listResult;

        }
        else {
            qCritical() << "错误查询指令：" << query.lastQuery();
            qCritical() << "数据查询错误：" << query.lastError();
        }
    }

    QVariantList lastListResult;
    if(listResult.length()){
        lastListResult.append(listResult.last());
    }

    qDebug() << "最新消息为："<< lastListResult;
    return lastListResult;
}

QVariantList DBManager::queryUserInfo(QString table)
{
    QVariantList listResult;
    QVariantMap mapResult;
    if(dbStatus){
        QSqlQuery query;
        bool success = query.exec(QString("SELECT number, name, img, ip, port FROM %1").arg(table));
        if(success){

            qDebug() << "数据查询成功："<< query.lastQuery();
            while(query.next()){
                QString number = query.value(0).toString();
                QString name= query.value(1).toString();
                QString img = query.value(2).toString();
                QString ip = query.value(3).toString();
                QString port = query.value(4).toString();

                mapResult.clear();
                mapResult.insert("number", number);
                mapResult.insert("name", name);
                mapResult.insert("img", img);
                mapResult.insert("ip", ip);
                mapResult.insert("port", port);

                listResult.append(mapResult);
            }
            qDebug() << "数据查询成功："<< listResult;

        }
        else {
            qCritical() << "错误查询指令：" << query.lastQuery();
            qCritical() << "数据查询错误：" << query.lastError();
        }
    }
    return listResult;
}

bool DBManager::updateMachineInfo(QString table)
{

    QList<QNetworkInterface> interface = QNetworkInterface::allInterfaces();
    QString ip;
    for(int i=0;i<interface.length();i++){
        if(interface.at(i).type() != QNetworkInterface::Virtual
            && interface.at(i).type() != QNetworkInterface::Loopback
            && interface.at(i).type() != QNetworkInterface::Unknown
            && interface.at(i).flags().testFlags(QNetworkInterface::IsUp)
            && interface.at(i).flags().testFlags(QNetworkInterface::IsRunning)
            && interface.at(i).flags().testFlags(QNetworkInterface::CanMulticast)
            && interface.at(i).flags().testFlags(QNetworkInterface::CanBroadcast)){

//            qDebug() << "interface：" << interface.at(i);
            QList<QNetworkAddressEntry> listEntries = interface.at(i).addressEntries();
            for(int j=0;j<listEntries.length();j++){
                if(listEntries.at(j).ip().protocol() == QHostAddress::IPv4Protocol){
                    ip = listEntries.at(j).ip().toString();
                    qDebug() << "IP：" << ip;

                }
            }

        }


    }

    QString port = "10101";
    if(dbStatus){
        QSqlQuery query;
        query.exec(QString("SELECT number, port FROM %1").arg(table));
        query.next();
        QString number = query.value(0).toString();
        QString _port = query.value(1).toString();
        bool success = false;
        if(_port==""||_port=="10101"){
            success = query.exec(QString("UPDATE %1 SET ip='%2', port='%3' WHERE number='%4'").arg(table, ip, port, number));
        }
        else {
            success = query.exec(QString("UPDATE %1 SET ip='%2' WHERE number='%3'").arg(table, ip, number));
        }

        if(success){

            qDebug() << "数据查询成功："<< query.lastQuery();
            return true;
        }
        else {
            qCritical() << "错误查询指令：" << query.lastQuery();
            qCritical() << "数据查询错误：" << query.lastError();
        }
    }
    return false;
}

void DBManager::insertUserInfo(QString table, QString name, QString number)
{
    if(dbStatus){
        QSqlQuery query;
        query.exec(QString("SELECT id FROM %1").arg(table));
        query.next();
        QString id = query.value(0).toString();
        if(id!="1"){
            query.exec(QString("DROP TABLE %1").arg(table));
            query.exec("CREATE TABLE IF NOT EXISTS UserInfo("
                       "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
                       "number VARCHAR(20) NOT NULL, "
                       "name VARCHAR(20) NOT NULL, "
                       "img TEXT, "
                       "ip VARCHAR(20) NOT NULL, "
                       "port VARCHAR(20) NOT NULL)");
            query.exec(QString("INSERT INTO %1(name,number,ip,port) VALUES "
                               "('%2', '%3', '', '')").arg(table, name, number));
            qDebug() << "查询指令："<< query.lastQuery();
            qDebug() << "初始化名：" << name;
            qDebug() << "初始化账号：" << number;
        }

    }
}

bool DBManager::updatePort(QString table, QString port)
{
    if(dbStatus){
        QSqlQuery query;
        query.exec(QString("SELECT number FROM %1").arg(table));
        query.next();
        QString number = query.value(0).toString();
        bool success = false;

        success = query.exec(QString("UPDATE %1 SET port='%2' WHERE number='%3'").arg(table, port, number));


        if(success){

            qDebug() << "数据查询成功："<< query.lastQuery();
            return true;
        }
        else {
            qCritical() << "错误查询指令：" << query.lastQuery();
            qCritical() << "数据查询错误：" << query.lastError();
        }
    }
    return false;
}

bool DBManager::delPerson(QString ip, QString port)
{
    if(dbStatus){
        QSqlQuery query;
        bool success = false;

        success = query.exec(QString("DELETE FROM TotalPerson WHERE ip = '%1' AND port = '%2';").arg(ip, port));

        if(success){

            qDebug() << "数据查询成功："<< query.lastQuery();
            return true;
        }
        else {
            qCritical() << "错误查询指令：" << query.lastQuery();
            qCritical() << "数据查询错误：" << query.lastError();
        }
    }
    return false;
}
